Namespace persistent
    Public Class BillingnetUnitUsageLogging

        Public Shared Function GetUsersList() As DataTable
            Return persistent.myDBConn.myORATable("select userid, username from login_user order by username")
        End Function

        Public Shared Function GetUnitsList() As DataTable
            Dim dtMenu As DataTable = persistent.myDBConn.myORATable _
            ( _
                "select * from billintra.menu2 " & _
                vbNewLine & "order by parent_menu_id nulls first, display_order, menu_title" _
            )

            Dim dtMenuToReturn As DataTable = dtMenu.Clone()

            Dim mainMenus As New DataView(dtMenu, "PARENT_MENU_ID IS NULL", "DISPLAY_ORDER", DataViewRowState.CurrentRows)

            For Each dr As DataRowView In mainMenus
                dtMenuToReturn.Rows.Add(dr.Row.ItemArray)

                addSubMenus(dtMenu, dr("MENU_ID"), 1, dtMenuToReturn)

            Next

            Return dtMenuToReturn
        End Function


        Private Shared Sub addSubMenus(ByVal dtMenu As DataTable, ByVal parentMenuID As Integer, ByVal menuLevel As Integer, ByVal dtMenuToReturn As DataTable)
            Dim mainMenus As New DataView(dtMenu, "PARENT_MENU_ID = " & parentMenuID, "DISPLAY_ORDER", DataViewRowState.CurrentRows)
            Dim rowValuesArray As Object()

            For Each dr As DataRowView In mainMenus
                rowValuesArray = dr.Row.ItemArray
                rowValuesArray(1) = CStr(rowValuesArray(1)).PadLeft(CStr(rowValuesArray(1)).Length + (menuLevel * 5), "-"c)

                dtMenuToReturn.Rows.Add(rowValuesArray)

                addSubMenus(dtMenu, dr("MENU_ID"), menuLevel + 1, dtMenuToReturn)

            Next

        End Sub

        Public Shared Function GetReportQuery _
        ( _
            ByVal startDate As Date, _
            ByVal endDate As Date, _
            Optional ByVal pageID As Integer = Integer.MinValue, _
            Optional ByVal userID As String = "", _
            Optional ByVal includeRequesterDetail As Boolean = False, _
            Optional ByVal includeRequestDateDetail As Boolean = False, _
            Optional ByVal getJustItemCount As Boolean = False _
        ) As String
            Return String.Format _
            ( _
                "select " & IIf(getJustItemCount, "count(*) ", "*") & " from " & _
                vbNewLine & "(" & _
                vbNewLine & "   select " & _
                vbNewLine & "   menu_table.menu_title unit_name " & _
                IIf(includeRequesterDetail, vbNewLine & ", nvl(lu.username, requester) requester_name", ", null requester_name") & _
                IIf(includeRequestDateDetail, vbNewLine & ", request_date ", ", null request_date") & _
                vbNewLine & "   , count(*) request_count " & _
                vbNewLine & "   from eu_bill.billingnet_unit_usage_log2 log_table, billintra.login_user lu, billintra.menu2 menu_table " & _
                vbNewLine & "   where lu.userid (+) = log_table.requester " & _
                vbNewLine & "   and menu_table.menu_id (+)= log_table.page_id " & _
                vbNewLine & "   and log_table.request_date >= to_date('{0}', 'dd.MM.yyyy') " & _
                vbNewLine & "   and log_table.request_date < to_date('{1}', 'dd.MM.yyyy')" & _
                vbNewLine & "   {2}" & _
                vbNewLine & "   {3}" & _
                vbNewLine & "   group by menu_table.menu_title{4}{5} " & _
                vbNewLine & ")dx " & _
                IIf _
                ( _
                Not getJustItemCount, _
                vbNewLine & "order by {6} unit_name {7}", _
                "" _
                ), _
                startDate.ToString("dd.MM.yyyy"), endDate.ToString("dd.MM.yyyy"), _
                IIf(pageID > Integer.MinValue, "and log_table.page_id = " & pageID, "--"), _
                IIf(userID <> String.Empty, "and lower(log_table.requester) = '" & userID.ToLower() & "'", "--"), _
                IIf(includeRequesterDetail, ", requester, lu.username", ""), _
                IIf(includeRequestDateDetail, ", request_date", ""), _
                IIf(includeRequestDateDetail, "request_date desc, ", ""), _
                IIf(includeRequestDateDetail, ", requester_name", "") _
            )
        End Function

        Public Shared Sub HandleRequestLogging()
            If Not System.Web.HttpContext.Current.Handler Is Nothing Then
                Try

                    Dim request As System.Web.HttpRequest = System.Web.HttpContext.Current.Request
                    Dim page As System.Web.UI.Page = CType(System.Web.HttpContext.Current.Handler, System.Web.UI.Page)

                    If Not (request Is Nothing OrElse page Is Nothing OrElse page.Session Is Nothing OrElse page.IsPostBack) Then
                        LogRequestedPage(System.Web.HttpContext.Current.Request.Url.PathAndQuery, page.Session("user"))
                    End If

                Catch ex As Exception

                End Try
            End If

        End Sub

        Public Shared Function IsUnitStub(ByVal pagePath As String, ByRef pageID As Object) As Boolean
            pageID = persistent.myDBConn.ORAScalar _
            ( _
                String.Format _
                ( _
                    vbNewLine & "select menu_id page_id from billintra.menu2 " & _
                    vbNewLine & "where lower(menu_link) like lower('{0}') and rownum = 1 ", _
                    pagePath.ToLower(System.Globalization.CultureInfo.CreateSpecificCulture("en-US")) _
                ) _
            )

            Return Not (pageID Is Nothing OrElse IsDBNull(pageID) OrElse pageID.ToString().Trim() = String.Empty)

        End Function

        Public Shared Function LogRequestedPage(ByVal pagePath As String, ByVal userID As String) As Boolean

            Dim pageID As Object

            If IsUnitStub(pagePath, pageID) Then

                persistent.myDBConn.ORAExecute _
                ( _
                    String.Format _
                    ( _
                        " insert into eu_bill.billingnet_unit_usage_log2 values ('{0}', sysdate, '{1}', {2})", _
                        pagePath, userID, pageID _
                    ) _
                )

            End If

        End Function


    End Class
End Namespace